#! /bin/bash
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "------------------因为采用了动态分区插入技术 因此需要设置相关参数---------------
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
set hive.support.concurrency=false;

--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;



SELECT
        ----------维度
        CASE WHEN GROUPING(itcast_school_id) = 0
                then itcast_school_id
                ELSE NULL END AS itcast_school_id,      ----校区id
        CASE WHEN GROUPING(itcast_school_id) = 0
                then itcast_school_name
                ELSE NULL END AS itcast_school_name,      ----校区名字
        CASE WHEN GROUPING(itcast_subject_id) = 0
                then itcast_subject_id
                ELSE NULL END AS itcast_subject_id,      ----学科id
        CASE WHEN GROUPING(itcast_subject_id) = 0
                then itcast_subject_name
                ELSE NULL END AS itcast_subject_name,      ----学科名字
        CASE WHEN GROUPING(line_type) = 0
                then line_type
                ELSE NULL END AS line_type,      ----线上线下
        CASE WHEN GROUPING(origin_channel) = 0
                then origin_channel
                ELSE NULL END AS origin_channel,      ----来源渠道
        CASE WHEN GROUPING(department_id) = 0
                then department_id
                ELSE NULL END AS department_id,      ----部门id
        CASE WHEN GROUPING(department_id) = 0
                then department_name
                ELSE NULL END AS department_name,      ----部门名字
         CASE
                    when grouping(line_type,itcast_school_id,origin_channel) = 0  THEN 'line_school_subject'
                    WHEN grouping(line_type,itcast_subject_id) = 0 THEN 'line_subject'
                    WHEN grouping(line_type,itcast_school_id) = 0 THEN 'line_school'
                    when grouping(itcast_school_id) = 0 THEN 'school'
                    WHEN grouping(line_type,origin_channel) = 0 THEN 'line_origin'
                    WHEN grouping(line_type,department_id) = 0 THEN 'line_dept'
                    WHEN grouping(pay_date) = 0 THEN 'all'
                    ELSE 'other' END AS group_type,
        CASE
            when grouping(line_type,itcast_school_id,origin_channel) = 0  THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,itcast_subject_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,itcast_school_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            when grouping(itcast_school_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,origin_channel) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,department_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(pay_date) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            ELSE null END AS group_typesignup_count,         ---统计不同类型的报名人数
       pay_date AS date_type,
       '2023-05-30' AS dt
FROM oe_dwb.dwb_signup_message
GROUP BY
GROUPING SETS (
    --天
    (pay_date),                                                                                          --日期
    (pay_date, itcast_school_id, itcast_school_name),                                                    --日期+学校
    (pay_date, line_type, itcast_school_id, itcast_school_name),                                         --日期+线上线下+学校
    (pay_date, line_type, itcast_subject_id, itcast_subject_name),                                       --日期+线上线下+学科
    (pay_date, line_type, itcast_school_id, itcast_school_name, itcast_subject_id, itcast_subject_name), --日期+线上线下+学校+学科
    (pay_date, line_type, origin_channel),                                                               --日期+线上线下+来源渠道
    (pay_date, line_type, department_id, department_name)                                                --日期+线上线下+咨询中心
    );


    --意向数
SELECT
    --维度
    relationship_create_date AS date_type,
    line_type,
    --指标
    sum(if(signup_type = '已报名' ,1,0)) AS signup_count,
    count(relationship_id) AS relation_count,
    '2023-05-30' AS dt
FROM oe_dwb.dwb_signup_message
WHERE relationship_create_date IS NOT NULL
GROUP BY relationship_create_date,line_type;
------有效线索数
    SELECT
        --维度
        clue_create_date                   AS date_type,
        line_type,
        --指标
        SUM(if(signup_type = '已报名', 1, 0)) AS signup_count,
        COUNT(clue_id)                     AS clue_count,
        '2023-05-30' AS dt
    FROM oe_dwb.dwb_signup_message
    GROUP BY clue_create_date, line_type;

WITH pay_count AS (
SELECT
        ----------维度
        CASE WHEN GROUPING(itcast_school_id) = 0
                then itcast_school_id
                ELSE NULL END AS itcast_school_id,      ----校区id
        CASE WHEN GROUPING(itcast_school_id) = 0
                then itcast_school_name
                ELSE NULL END AS itcast_school_name,      ----校区名字
        CASE WHEN GROUPING(itcast_subject_id) = 0
                then itcast_subject_id
                ELSE NULL END AS itcast_subject_id,      ----学科id
        CASE WHEN GROUPING(itcast_subject_id) = 0
                then itcast_subject_name
                ELSE NULL END AS itcast_subject_name,      ----学科名字
        CASE WHEN GROUPING(line_type) = 0
                then line_type
                ELSE NULL END AS line_type,      ----线上线下
        CASE WHEN GROUPING(origin_channel) = 0
                then origin_channel
                ELSE NULL END AS origin_channel,      ----来源渠道
        CASE WHEN GROUPING(department_id) = 0
                then department_id
                ELSE NULL END AS department_id,      ----部门id
        CASE WHEN GROUPING(department_id) = 0
                then department_name
                ELSE NULL END AS department_name,      ----部门名字
         CASE
                    when grouping(line_type,itcast_school_id,origin_channel) = 0  THEN 'line_school_subject'
                    WHEN grouping(line_type,itcast_subject_id) = 0 THEN 'line_subject'
                    WHEN grouping(line_type,itcast_school_id) = 0 THEN 'line_school'
                    when grouping(itcast_school_id) = 0 THEN 'school'
                    WHEN grouping(line_type,origin_channel) = 0 THEN 'line_origin'
                    WHEN grouping(line_type,department_id) = 0 THEN 'line_dept'
                    WHEN grouping(pay_date) = 0 THEN 'all'
                    ELSE 'other' END AS group_type,
        CASE
            when grouping(line_type,itcast_school_id,origin_channel) = 0  THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,itcast_subject_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,itcast_school_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            when grouping(itcast_school_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,origin_channel) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,department_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(pay_date) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            ELSE null END AS group_typesignup_count,         ---统计不同类型的报名人数
       pay_date AS date_type,
       '2023-05-30' AS dt
FROM oe_dwb.dwb_signup_message
GROUP BY
GROUPING SETS (
        --天
        (pay_date),
        (pay_date,itcast_school_id,itcast_school_name),                         --日期+学校
        (pay_date,line_type,itcast_school_id,itcast_school_name),               --日期+线上线下+学校
        (pay_date,line_type,itcast_subject_id,itcast_subject_name),             --日期+线上线下+学科
        (pay_date,line_type,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name),      --日期+线上线下+学校+学科
        (pay_date,line_type,origin_channel),                             --日期+线上线下+来源渠道
        (pay_date,line_type,department_id,department_name)           --日期+线上线下+咨询中心
)),relation_count AS (
--意向数
SELECT
    --维度
    relationship_create_date AS date_type,
    line_type,
    --指标
    sum(if(signup_type = '已报名' ,1,0)) AS signup_count,
    count(relationship_id) AS relation_count,
    '2023-05-30' AS dt
FROM oe_dwb.dwb_signup_message
WHERE relationship_create_date IS NOT NULL
GROUP BY relationship_create_date,line_type),
clue_count AS (
------有效线索数
    SELECT
        --维度
        clue_create_date                   AS date_type,
        line_type,
        --指标
        SUM(if(signup_type = '已报名', 1, 0)) AS signup_count,
        COUNT(clue_id)                     AS clue_count,
        '2023-05-30' AS dt
    FROM oe_dwb.dwb_signup_message
    GROUP BY clue_create_date, line_type
)
SELECT itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       pc.line_type,
       origin_channel,
       department_id,
       department_name,
       group_type,
       group_typesignup_count,
       pc.dt,
       rc.line_type,
       rc.signup_count,
       relation_count,
       rc.dt,
       cc.line_type,
       cc.signup_count,
       clue_count,
       pc.date_type,
       rc.date_type,
       cc.date_type,
       cc.dt
FROM pay_count pc FULL JOIN relation_count rc ON pc.date_type = rc.date_type
FULL JOIN clue_count cc ON cc.date_type = pc.date_type;


---插入临时表


CREATE TABLE oe_dws.tmp1 AS
SELECT *
FROM (WITH pay_count AS (
SELECT
        ----------维度
        CASE WHEN GROUPING(itcast_school_id) = 0
                then itcast_school_id
                ELSE NULL END AS itcast_school_id,      ----校区id
        CASE WHEN GROUPING(itcast_school_id) = 0
                then itcast_school_name
                ELSE NULL END AS itcast_school_name,      ----校区名字
        CASE WHEN GROUPING(itcast_subject_id) = 0
                then itcast_subject_id
                ELSE NULL END AS itcast_subject_id,      ----学科id
        CASE WHEN GROUPING(itcast_subject_id) = 0
                then itcast_subject_name
                ELSE NULL END AS itcast_subject_name,      ----学科名字
        CASE WHEN GROUPING(line_type) = 0
                then line_type
                ELSE NULL END AS line_type,      ----线上线下
        CASE WHEN GROUPING(origin_channel) = 0
                then origin_channel
                ELSE NULL END AS origin_channel,      ----来源渠道
        CASE WHEN GROUPING(department_id) = 0
                then department_id
                ELSE NULL END AS department_id,      ----部门id
        CASE WHEN GROUPING(department_id) = 0
                then department_name
                ELSE NULL END AS department_name,      ----部门名字
         CASE
                    when grouping(line_type,itcast_school_id,itcast_subject_id) = 0  THEN 'line_school_subject'
                    WHEN grouping(line_type,itcast_subject_id) = 0 THEN 'line_subject'
                    WHEN grouping(line_type,itcast_school_id) = 0 THEN 'line_school'
                    when grouping(itcast_school_id) = 0 THEN 'school'
                    WHEN grouping(line_type,origin_channel) = 0 THEN 'line_origin'
                    WHEN grouping(line_type,department_id) = 0 THEN 'line_dept'
                    WHEN grouping(pay_date) = 0 THEN 'all'
                    ELSE 'other' END AS group_type,
        CASE
            when grouping(line_type,itcast_school_id,origin_channel) = 0  THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,itcast_subject_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,itcast_school_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            when grouping(itcast_school_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,origin_channel) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(line_type,department_id) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            WHEN grouping(pay_date) = 0 THEN count(if(signup_type = '已报名',signup_type,null))
            ELSE null END AS group_typesignup_count,         ---统计不同类型的报名人数
       pay_date AS date_type,
       '2023-05-30' AS dt
FROM oe_dwb.dwb_signup_message
GROUP BY
GROUPING SETS (
        --天
        (pay_date),
        (pay_date,itcast_school_id,itcast_school_name),                         --日期+学校
        (pay_date,line_type,itcast_school_id,itcast_school_name),               --日期+线上线下+学校
        (pay_date,line_type,itcast_subject_id,itcast_subject_name),             --日期+线上线下+学科
        (pay_date,line_type,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name),      --日期+线上线下+学校+学科
        (pay_date,line_type,origin_channel),                             --日期+线上线下+来源渠道
        (pay_date,line_type,department_id,department_name)           --日期+线上线下+咨询中心
)),relation_count AS (
--意向数
SELECT
    --维度
    relationship_create_date AS date_type,
    line_type,
    --指标
    sum(if(signup_type = '已报名' ,1,0)) AS signup_count,
    count(relationship_id) AS relation_count,
    '2023-05-30' AS dt
FROM oe_dwb.dwb_signup_message
WHERE relationship_create_date IS NOT NULL
GROUP BY relationship_create_date,line_type),
clue_count AS (
------有效线索数
    SELECT
        --维度
        clue_create_date                   AS date_type,
        line_type,
        --指标
        SUM(if(signup_type = '已报名', 1, 0)) AS signup_count,
        COUNT(clue_id)                     AS clue_count,
        '2023-05-30' AS dt
    FROM oe_dwb.dwb_signup_message
    GROUP BY clue_create_date, line_type
)
SELECT itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       pc.line_type,
       origin_channel,
       department_id,
       department_name,
       group_type,
       group_typesignup_count AS group_type_signup_count,
       rc.line_type AS rc_line_type,
       rc.signup_count AS rc_signup_count,
       relation_count,
       cc.line_type AS cc_line_type,
       cc.signup_count AS cc_sigup_count,
       clue_count,
       pc.date_type AS pc_date_type,
       rc.date_type AS rc_date_type,
       cc.date_type AS cc_date_type,
       cc.dt
FROM pay_count pc FULL JOIN relation_count rc ON pc.date_type = rc.date_type
FULL JOIN clue_count cc ON cc.date_type = pc.date_type);



-----插入数据到dws表


CREATE TABLE oe_dws.dws_signup_message1 AS
SELECT * FROM oe_dws.tmp1;


"